#! /bin/bash
mysql -uroot -p123456 -e "create datebase online_edu"
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
/*
- 每天/每月/每个季度/每年总访问用户量、总访问IP个数、总访问Session个数
- 每天/每月/每个季度/每年全国各个地区访问用户量、访问IP个数、访问Session个数
- 每天/每月/每个季度/每年每个来源渠道的访问用户量、访问IP个数、访问Session个数
- 每天/每月/每个季度/每年每个搜索来源的访问用户量、访问IP个数、访问Session个数
- 每天/每月/每个季度/每年每个来源页面的访问用户量、访问IP个数、访问Session个数
- 每天/每月/每个季度/每年全国各个地区咨询率：咨询率 = 咨询人数 / 访问人数
 */

select * from view_consult_dws.dws_view;
-- - 每天/每月/每个季度/每年总访问用户量、总访问IP个数、总访问Session个数
SELECT
yearinfo,
session_cnt,
ip_cnt,
id_cnt
from  view_consult_dws.dws_view where group_type='all' and time_type = 'year';

SELECT
yearinfo,
monthinfo,
session_cnt,
ip_cnt,
id_cnt
from  view_consult_dws.dws_view where group_type='all' and time_type = 'month';

SELECT
yearinfo,
monthinfo,
dayinfo,
session_cnt,
ip_cnt,
id_cnt
from  view_consult_dws.dws_view where group_type='all' and time_type = 'day';

-- - 每天/每月/每个季度/每年全国各个地区访问用户量、访问IP个数、访问Session个数 ok
create table mysql.online_edu.view_area_year_count_0509 as
SELECT
yearinfo,
area,
country,
province,
city,
session_cnt,
ip_cnt,
id_cnt
from  view_consult_dws.dws_view where group_type='area' and time_type = 'year';

--- 每天/每月/每个季度/每年每个来源渠道的访问用户量、访问IP个数、访问Session个数 ok
create table mysql.online_edu.view_channel_day_count_0509 as
SELECT
yearinfo,
monthinfo,
quarterinfo,
dayinfo,
origin_channel,
session_cnt,
ip_cnt,
id_cnt
from  view_consult_dws.dws_view where group_type='origin_channel' and time_type = 'day' and yearinfo != '2031' and origin_channel is not null;


-- 每天/每月/每个季度/每年每个搜索来源的访问用户量、访问IP个数、访问Session个数 ok
create table mysql.online_edu.view_seo_day_count_0509 as
SELECT
yearinfo,
monthinfo,
quarterinfo,
dayinfo,
seo_source,
session_cnt,
ip_cnt,
id_cnt
from  view_consult_dws.dws_view where group_type='seo_source' and time_type = 'day' and yearinfo != '2031' and seo_source is not null;

-- - 每天/每月/每个季度/每年每个来源页面的访问用户量、访问IP个数、访问Session个数
-- create table mysql.online_edu.view_seo_day_count_0509 as
create table mysql.online_edu.view_url_year_count_idtop10_0509 as
SELECT
yearinfo,
from_url,
session_cnt,
ip_cnt,
id_cnt
from  view_consult_dws.dws_view where group_type='from_url' and time_type = 'year' and yearinfo != '2031' and from_url is not null order by id_cnt desc limit 10;

-- 每天/每月/每个季度/每年全国各个地区咨询率：咨询率 = 咨询人数 / 访问人数
create table mysql.online_edu.view_consult_area_year_ratio_0509 as
with a as (
select
yearinfo,
area,
country,
province,
city,
session_cnt,
ip_cnt,
id_cnt
from view_consult_dws.dws_consult where group_type='area' and time_type='year'),

b as (
select
yearinfo,
area,
country,
province,
city,
session_cnt,
ip_cnt,
id_cnt
from view_consult_dws.dws_view where group_type='area' and time_type='year')

select
b.yearinfo,
b.area,
b.country,
b.province,
b.city,
b.session_cnt as view_session_count,
b.ip_cnt as view_ip_count,
b.id_cnt as view_id_count,
a.session_cnt as consult_session_count,
a.ip_cnt as consult_ip_count,
a.id_cnt as consult_id_count,
cast(cast(a.session_cnt as decimal(38,5))/cast(b.session_cnt as decimal(38,5)) as decimal(5,2)) session_ratio,
cast(cast(a.ip_cnt as decimal(38,5))/cast(b.ip_cnt as decimal(38,5)) as decimal(5,2)) ip_ratio,
cast(cast(a.id_cnt as decimal(38,5))/cast(b.id_cnt as decimal(38,5)) as decimal(5,2)) id_ratio

from b left join a on  a.yearinfo = b.yearinfo and a.area = b.area and a.province = b.province and a.country = b.country and a.city = b.city





create table mysql.online_edu.consult_area_year_count_0509 as
SELECT
yearinfo,
area,
country,
province,
city,
session_cnt,
ip_cnt,
id_cnt
from view_consult_dws.dws_consult where group_type='area' and time_type='year';
-- 每天/每月/每个季度/每年全国各个地区咨询人数
"
